package com.auto.code.core.service.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import com.auto.code.core.domain.GenFieldEntity;
import com.auto.code.core.domain.GenTableEntity;
import com.auto.code.core.service.ReadTableService;
import com.auto.code.core.util.ConnectionUtil;
import com.auto.code.core.util.NameUtil;
import com.auto.code.core.util.TableHandlerUtil;
import com.mysql.jdbc.Statement;

@Service
public class ReadTableServiceImpl implements ReadTableService{
	private static String TABLE_SQL = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME = '%s' AND TABLE_SCHEMA = '%s'";

	private static String ALL_TABLE_SQL = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '%s'";

	private static String FIELDS_SQL = "SELECT column_name as fieldName, data_type as fieldType, column_comment, numeric_precision, numeric_scale as scale, character_maximum_length as charmaxLength,is_nullable as nullable from information_schema.columns where table_name = '%s' and table_schema = '%s'";

//	private static String SCHEMA_SQL = "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME != 'information_schema' AND SCHEMA_NAME !=  'mysql'";
	    
	@Override
	public GenTableEntity readField(String dbName, String tableName) throws Exception {
		try {
            GenTableEntity entity = getTableEntiy(dbName, tableName, TABLE_SQL);
            entity.setName(NameUtil.getEntityHumpName(entity.getTableName()));
            entity.setFields(getTableFields(dbName, tableName, FIELDS_SQL));
            TableHandlerUtil.handlerFields(entity.getFields());
            return entity;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("获取表格数据发生异常");
        }
	}

	@Override
	public List<GenTableEntity> getAllTable(String dbName) throws Exception {
		Statement statement = null;
        List<GenTableEntity> list = new ArrayList<GenTableEntity>();
        try {
            ResultSet rs = ConnectionUtil.createStatement().executeQuery(String.format(ALL_TABLE_SQL, dbName));
            String dbTableName = null;
            String comment = null;
            while (rs.next()) {
                dbTableName = rs.getString(1);
                comment = rs.getString(2);
                if (StringUtils.isEmpty(dbTableName)) {
                    throw new Exception("表不存在");
                } else {
                    GenTableEntity entity = new GenTableEntity();
                    if (StringUtils.isNotEmpty(comment)) {
                        entity.setChinaName(handlerTableComment(comment));
                    }
                    entity.setTableName(dbTableName);
                    list.add(entity);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("查询表是否存在发生异常", e);
        } finally {
            if (statement != null) {
                statement.close();
                statement = null;
            }
        }
        return list;
	}
	
	
	private GenTableEntity  getTableEntiy(String dbName, String tableName, String sql) throws Exception {
        Statement statement = null;
        try {
            ResultSet rs = ConnectionUtil.createStatement().executeQuery(String.format(sql, tableName, dbName));
            String dbTableName = null;
            String comment = null;
            while (rs.next()) {
                dbTableName = rs.getString(1);
                comment = rs.getString(2);
            }
            if (StringUtils.isEmpty(dbTableName)) {
                throw new Exception("表不存在");
            } else {
                GenTableEntity entity = new GenTableEntity();
                if (StringUtils.isNotEmpty(comment)) {
                    entity.setChinaName(handlerTableComment(comment));
                }
                entity.setTableName(dbTableName);
                return entity;
            }
        } catch (Exception e) {
        	e.printStackTrace();
            throw new Exception("查询表是否存在发生异常", e);
        } finally {
            if (statement != null) {
                statement.close();
                statement = null;
            }
        }
    }
	
	private List<GenFieldEntity> getTableFields(String dbName, String tableName, String sql) throws Exception {
        List<GenFieldEntity> list = new ArrayList<GenFieldEntity>();
        Statement statement = null;
        try {
            ResultSet rs = ConnectionUtil.createStatement()
                    .executeQuery(String.format(sql, tableName, dbName));
            GenFieldEntity field;
            while (rs.next()) {
                field = new GenFieldEntity();
//                if (StringUtils.isNotEmpty(rs.getString("charmaxLength"))) {
//                    field.setFieldLength(rs.getInt("charmaxLength"));
//                } else {
//                    field.setFieldLength(rs.getInt("numeric_precision"));
//                }
                field.setComment(rs.getString("column_comment"));
                field.setFieldName(rs.getString("fieldName"));
                field.setNotNull("Y".equalsIgnoreCase(rs.getString("nullable")) ? 2 : 1);
                field.setFieldType(rs.getString("fieldType"));
                field.setFieldPointLength(rs.getInt("scale"));
                list.add(field);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("查询表是否存在发生异常", e);
        } finally {
            if (statement != null) {
                statement.close();
                statement = null;
            }
        }
        return list;
    }
	
	protected String handlerTableComment(String comment) {
        if (comment.contains(";")) {
            return comment.split(";")[0];
        }
        if (comment.startsWith("InnoDB free")) {
            return null;
        }
        return comment;
    }
}
